ALTER TABLE Promotion
ADD Ready BIT NOT NULL DEFAULT(0)
GO
CREATE PROCEDURE [dbo].[spAPI_Promotion_Submit]
(
	@Token VARCHAR(255),
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @IdResult INT
	DECLARE @BusinessId INT
		
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
			UPDATE Promotion
			SET Ready = 1
			WHERE (PromotionId = @PromotionId)
		ELSE
			SET @IdResult = -1

	SELECT @IdResult AS IdResult
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetDeals]
(
	@Token VARCHAR(255),
	@Latitude FLOAT,
	@Longtitude FLOAT
)
as
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
		SELECT @Result = (SELECT Promotion.PromotionId, Promotion.Name AS PromotionName,
								Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
								ISNULL(vwReview.CountReviews, 0) AS Reviews, 15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day]
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM PromotionPush INNER JOIN Promotion ON PromotionPush.PromotionId = Promotion.PromotionId
											LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
											LEFT JOIN (SELECT BusinessId, 
													COUNT(*) AS CountReviews, AVG(CONVERT(FLOAT, Rating)) AS AvgRating 
													FROM BusinessReview
													GROUP BY BusinessId) AS vwReview ON Business.BusinessId = vwReview.BusinessId
							WHERE (PromotionPush.UserId = @UserId)
								AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
								AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))		
								AND (Promotion.StatusCode = 'ACT')
								AND (Promotion.Deleted = 0)
								AND (Promotion.Ready = 1)
							ORDER BY PromotionPush.DatePush DESC
							FOR XML PATH ('Promotion'), ROOT('Deals'))
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_Promotion_Checkin]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@Code VARCHAR(4)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @IdResult INT
	DECLARE @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 
				FROM Promotion 
				WHERE PromotionId = @PromotionId 
					AND CheckinCode = @Code 
					AND StatusCode = 'ACT'
					AND Deleted = 0
					AND Ready = 1)
		BEGIN
			IF NOT EXISTS(SELECT 1 FROM PromotionCheckin WHERE PromotionId = @PromotionId AND UserId = @UserId)
				INSERT INTO PromotionCheckin
				(PromotionId, UserId)
				VALUES (@PromotionId, @UserId)
		END			
		ELSE
			SET @IdResult = -1

	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result		
END
GO
ALTER PROCEDURE [dbo].[spAPI_Promotion_Push]
(
	@Token VARCHAR(255),
	@Latitude FLOAT,
	@Longtitude FLOAT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	BEGIN
		CREATE TABLE #tmpPromotions
		(
			PromotionId INT
		)
		
		BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId)
			SELECT Promotion.PromotionId
			FROM Promotion (NOLOCK) LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
						LEFT JOIN ProximityRadius (NOLOCK) ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
			WHERE /*(NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = Promotion.PromotionId)))
				AND*/ (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
				AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))		
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (Promotion.StatusCode = 'ACT')
				
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.PromotionText, Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
								ISNULL(vwReview.CountReviews, 0) AS Reviews, 15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory (NOLOCK) LEFT JOIN Category (NOLOCK) ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day] (NOLOCK)
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions INNER JOIN Promotion (NOLOCK) ON #tmpPromotions.PromotionId = Promotion.PromotionId
											LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN (SELECT BusinessId, 
													COUNT(*) AS CountReviews, AVG(CONVERT(FLOAT, Rating)) AS AvgRating 
													FROM BusinessReview (NOLOCK)
													GROUP BY BusinessId) AS vwReview ON Business.BusinessId = vwReview.BusinessId
							FOR XML PATH ('Promotion'), ROOT('Promotions'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude)
			SELECT PromotionId, @UserId, GETDATE(), @Latitude, @Longtitude
			FROM #tmpPromotions
			WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = #tmpPromotions.PromotionId)))
			
		COMMIT TRANSACTION
		
		DROP TABLE #tmpPromotions
	END
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
